﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using OgilvyOne.UKNow.Model;
using ECCIT.Data;
using System.Data;

namespace OgilvyOne.UKNow.DAL
{

  
    /// <summary>
    /// 音频数据表访问操作类
    /// </summary>
    /// 
   public class AudioDal
    {
        int langid;

        public int Langid
        {
            get { return langid; }
            set { langid = value; }
        }
       public AudioDal(int lang_id)
       {
           Langid = lang_id;
       }
       public List<Audio> GetALLMod()
       {
           string sqlcmd = String.Format("select * from UKNOW_AUDIO where COL_LANG_ID={0} order by COL_AUDIO_ORDER asc", Langid);
           return Tomod(DBHelper.ExecuteDataSet(sqlcmd, null).Tables[0]);
       }
       /// <summary>
       /// 按Audio_id 单一或批量查询  查询
       /// </summary>
       /// <param name="id">Audio唯一标示id集合</param>
       /// <returns>Audio查询结果的实体集合</returns>
       public Audio GetAudioModsById(int id)
       {
           StringBuilder stb = new StringBuilder();
           stb.Append("select * from UKNOW_AUDIO where");
           stb.AppendFormat(" COL_AUDIO_ID={0}", id.ToString());
           
           return Tomod(DBHelper.ExecuteDataSet(stb.ToString(), null).Tables[0])[0];
         
       }
       /// <summary>
       /// 根据where 条件语句查询结果集
       /// </summary>
       /// <param name="sqlcmd">sql语句</param>
       /// <returns>结果实体集合</returns>
       private List<Audio> GetAudioModsByWhereSql(string sqlcmd)
       {
           StringBuilder stb = new StringBuilder();
           stb.AppendFormat("select * from UKNOW_AUDIO where {0} ", sqlcmd);
           return Tomod(DBHelper.ExecuteDataSet(stb.ToString(), null).Tables[0]);
          
       }
       /// <summary>
       /// 根据完整sql语句查询结果集
       /// </summary>
       /// <param name="sqlcmd">sql语句</param>
       /// <returns>结果实体集合</returns>
       private List<Audio> GetAudioModsBySql(string sqlcmd)
       {
           if (sqlcmd.ToLower().Contains("select"))
           {
               StringBuilder stb = new StringBuilder();
               stb.Append(sqlcmd);
               return Tomod( DBHelper.ExecuteDataSet(sqlcmd, null).Tables[0]);
              

           }
           else
           {
               return null;
           }
          

          
       }
       public  bool Add(Audio aud)
       {
           if (aud != null)
           {

               StringBuilder stb = new StringBuilder();
               stb.Append("insert into UKNOW_AUDIO (");
               stb.Append("COL_AUDIO_AUTHOR,COL_AUDIO_DESCRIPTION,COL_AUDIO_UPLOADDATATIME,COL_AUDIO_NAME,COL_AUDIO_ORDER,COL_AUDIO_BIGIMG,COL_AUDIO_THUMBIMG,COL_LANG_ID,COL_AUDIO_RELEASE,COL_AUDIO_FILEPATH,COL_AUDIO_COLUMN_ID,COL_AUDIO_LAYER_ID");
               stb.Append(") values(");
               stb.AppendFormat("'{0}','{1}','{2}','{3}',{4},'{5}','{6}',{7},'{8}','{9}',{10},{11})", aud.Author, aud.Description, aud.Uploaddatatime.ToString(), aud.Name, aud.Order.ToString(), aud.Bigimg, aud.Thumbimg,aud.Lang_id,aud.Release,aud.FilePath,aud.Column_id.ToString(),aud.Lang_id);

               return DBHelper.ExecuteNonQuery(stb.ToString()) > 0 ? true : false;
           }
           else
           {
               return false;
           }
       }
       public  bool Update(Audio aud)
       {
           if (aud != null)
           {

               StringBuilder stb = new StringBuilder();
               stb.Append("Update  UKNOW_AUDIO set");
               stb.AppendFormat(" COL_AUDIO_AUTHOR='{0}' ",aud.Author);
               stb.AppendFormat(" ,COL_AUDIO_DESCRIPTION='{0}'", aud.Description);
               stb.AppendFormat(" ,COL_AUDIO_UPLOADDATATIME='{0}'",aud.Uploaddatatime.ToString());
               stb.AppendFormat(" ,COL_AUDIO_NAME='{0}'",aud.Name);
               stb.AppendFormat(" ,COL_AUDIO_ORDER='{0}'",aud.Order.ToString());
               stb.AppendFormat(" ,COL_AUDIO_BIGIMG='{0}'",aud.Bigimg);
               stb.AppendFormat(" ,COL_AUDIO_THUMBIMG='{0}'", aud.Thumbimg);
               stb.AppendFormat(" ,COL_LANG_ID='{0}'", aud.Lang_id.ToString());
               stb.AppendFormat(" ,COL_AUDIO_RELEASE='{0}'", aud.Release);

               stb.AppendFormat(",COL_AUDIO_COLUMN_ID={0}", aud.Column_id);
               stb.AppendFormat(" ,COL_AUDIO_FILEPATH='{0}'",aud.FilePath);
               stb.AppendFormat(",COL_AUDIO_LAYER_ID='{0}'", aud.Layerid);
               
               stb.AppendFormat(" where COL_AUDIO_ID={0}",aud.Id);
               return  DBHelper.ExecuteNonQuery(stb.ToString())>0?true:false;
           }
           else
           {
               return false;
           }
       }
       public  bool Delete(int id)
       {
           StringBuilder stb = new StringBuilder();
           stb.AppendFormat("delete UKNOW_AUDIO where COL_AUDIO_ID={0}",id.ToString());
           return    DBHelper.ExecuteNonQuery(stb.ToString())>0?true:false;
   
       }
       private  List<Audio> Tomod(DataTable dt)
       {
           List<Audio> la = new List<Audio>();
           foreach (DataRow dr in dt.Rows)
           {
               Audio aud = new Audio(int.Parse(dr["COL_LANG_ID"].ToString()));
               aud.Id = int.Parse(dr["COL_AUDIO_ID"].ToString());
               aud.Author = dr["COL_AUDIO_AUTHOR"].ToString();
               aud.Bigimg = dr["COL_AUDIO_BIGIMG"].ToString();
               aud.Description = dr["COL_AUDIO_DESCRIPTION"].ToString();
               aud.FilePath = dr["COL_AUDIO_FILEPATH"].ToString();
               aud.Name = dr["COL_AUDIO_NAME"].ToString();
               aud.Order = int.Parse(dr["COL_AUDIO_ORDER"].ToString());
               aud.Thumbimg = dr["COL_AUDIO_THUMBIMG"].ToString();
               if (dr["COL_AUDIO_COLUMN_ID"] != DBNull.Value)
               {
                   aud.Column_id = Convert.ToInt32(dr["COL_AUDIO_COLUMN_ID"]);
               }
               if (dr["COL_AUDIO_TEMPLATE_ID"] != DBNull.Value)
               {
                   aud.Template_id = Convert.ToInt32(dr["COL_AUDIO_TEMPLATE_ID"]);
               }
               aud.Layerid = dr["COL_AUDIO_LAYER_ID"].ToString();
               if (dr["COL_AUDIO_UPLOADDATATIME"].ToString()!="")
	{
		 aud.Uploaddatatime =Convert.ToDateTime( dr["COL_AUDIO_UPLOADDATATIME"]);
	}

               aud.Release = Convert.ToBoolean(dr["COL_AUDIO_RELEASE"]);
               la.Add(aud);

               
           }
           return la;
         
       }
       public List<Audio> FindByNameAuthor(string sqlcmd)
       {

           StringBuilder stb = new StringBuilder();
           stb.AppendFormat(" COL_AUDIO_AUTHOR like '%{0}%'",sqlcmd);
           stb.AppendFormat(" or COL_AUDIO_NAME like'%{0}%'",sqlcmd);
           return GetAudioModsByWhereSql(stb.ToString());
       }
       public List<Audio> GetALLRelaseMod()
       {
           StringBuilder stb = new StringBuilder();
           stb.AppendFormat("COL_AUDIO_RELEASE='true'");
           return GetAudioModsByWhereSql(stb.ToString());
       }
       public List<Audio> GetALLnoRelaseMod()
       {
           StringBuilder stb = new StringBuilder();
           stb.AppendFormat("COL_AUDIO_RELEASE='false'");
           return GetAudioModsByWhereSql(stb.ToString());
       }
      
    
     

      


    }
}
